package org.compiere.report;

import java.io.File;
import java.io.FileOutputStream;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.Properties;
import java.util.logging.Level;

import org.adempiere.exceptions.AdempiereException;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
import org.compiere.model.MAccountCombination;
import org.compiere.model.MAttachmentEntry;
import org.compiere.model.MElementValue;
import org.compiere.model.MOrg;
import org.compiere.model.MProduct;
import org.compiere.process.ProcessInfo;
import org.compiere.process.ProcessInfoParameter;
import org.compiere.process.SvrProcess;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;

import extend.org.compiere.utils.Util;

public class MM_Form_296b extends SvrProcess {
	//Form_296b_
	/** Current context */
	private Properties m_ctx;
	private ProcessInfo pi;
	int p_Account_ID, p_M_Product_ID, p_P_Asset_Acct;
	
	Timestamp DateAcctBegin, DateAcctEnd;
	
	/** */
	String ResultPath;

	/**
	 * Prepare - e.g., get Parameters.
	 */
	@Override
	protected void prepare() {
		log.info("");
		m_ctx = Env.getCtx();
		pi = getProcessInfo();
		ProcessInfoParameter[] para = getParameter();
		for (int i = 0; i < para.length; i++) {
			String name = para[i].getParameterName();
			if (para[i].getParameter() == null)
				;
			else if (name.equals("P_Asset_Acct")) 
				p_P_Asset_Acct  = para[i].getParameterAsInt();
			else if (name.equals("M_Product_ID")) 
				p_M_Product_ID = para[i].getParameterAsInt();
			else if (name.equals("DateAcctBegin")) 
				DateAcctBegin = (Timestamp) para[i].getParameter();
			else if (name.equals("DateAcctEnd")) 
				DateAcctEnd = (Timestamp) para[i].getParameter();
			else
				log.log(Level.SEVERE, "Unknown Parameter: " + name);
		}

	}

	@Override
	protected String doIt() throws Exception {
		//File path
		ResultPath = "Form_296b_";
		Integer pathI = 0;
		boolean flag = true;
		while (flag) {
			File f = new File(Util.localFilePath + ResultPath + ".xls");
			try { 
				if (f.exists() && !f.delete()) {
					ResultPath = ResultPath + "(";
					ResultPath = ResultPath.substring(0, 11) + (++pathI).toString() + ")";  
				} else {
					flag = false;
				}
			} catch (Exception e) {
				e.printStackTrace();
				ResultPath = ResultPath + "(" +(++pathI).toString() + ")";
			}
		}
		ResultPath = Util.localFilePath + ResultPath + ".xls";
		MAttachmentEntry entry = Util.getAttachment(pi, m_ctx, ResultPath);
		if (entry == null)
			throw new AdempiereException(Msg.translate(m_ctx,"NotFoundTemplate"));

		HSSFWorkbook wb = new HSSFWorkbook(entry.getInputStream());
		HSSFSheet sheet = wb.getSheetAt(0);
		Row R = null;
		Cell C = null;
		p_Account_ID = new MAccountCombination(m_ctx, p_P_Asset_Acct, get_TrxName()).getAccount_ID();
		
		//Date Cell Styling
		CellStyle ds = wb.createCellStyle();
		ds.setBorderBottom((short)1);
		ds.setFont(sheet.getRow(7).getCell(0).getCellStyle().getFont(wb));
		ds.setDataFormat(wb.createDataFormat().getFormat("dd.MM.yyyy"));
		ds.setVerticalAlignment(CellStyle.ALIGN_CENTER);
		//DocType
		CellStyle dts = wb.createCellStyle();
		dts.setBorderBottom((short)1);
		dts.setFont(sheet.getRow(7).getCell(0).getCellStyle().getFont(wb));
		dts.setDataFormat(wb.createDataFormat().getFormat("dd.MM.yyyy"));
		dts.setVerticalAlignment(CellStyle.ALIGN_LEFT);
		//New Cell Style
		CellStyle cs = wb.createCellStyle();
		cs.setBorderBottom((short)1);
		cs.setBorderTop((short)1);
		cs.setBorderLeft((short)1);
		cs.setBorderRight((short)1);
		cs.setFont(sheet.getRow(7).getCell(0).getCellStyle().getFont(wb));
		cs.setVerticalAlignment(CellStyle.ALIGN_CENTER);
		
		//Header
		sheet.getRow(12).getCell(51).setCellValue((new SimpleDateFormat("d").format(DateAcctBegin)));
		sheet.getRow(12).getCell(54).setCellValue((new SimpleDateFormat("MMMMM").format(DateAcctBegin)));
		sheet.getRow(12).getCell(59).setCellValue((new SimpleDateFormat("Y").format(DateAcctBegin)));
		sheet.getRow(13).getCell(51).setCellValue((new SimpleDateFormat("d").format(DateAcctEnd)));
		sheet.getRow(13).getCell(54).setCellValue((new SimpleDateFormat("MMMMM").format(DateAcctEnd)));
		sheet.getRow(13).getCell(59).setCellValue((new SimpleDateFormat("Y").format(DateAcctEnd)));
		
		sheet.getRow(7).getCell(0).setCellValue(MOrg.get(m_ctx, Env.getAD_Org_ID(m_ctx)).getName());
		sheet.getRow(15).getCell(3).setCellValue((new MElementValue(m_ctx, p_Account_ID, get_TrxName()).getValue()) + "-" +(new MElementValue(m_ctx, p_Account_ID, get_TrxName()).getName()));
		sheet.getRow(15).getCell(39).setCellValue((new MProduct(m_ctx, p_M_Product_ID, get_TrxName()).getValue()) + '-' + (new MProduct(m_ctx, p_M_Product_ID, get_TrxName()).getName()));
		
		//Positions
		int row = 21;
		PreparedStatement pstmtturnover = null;
		ResultSet rsturnover = null;
	
		String sqlturnover =  "select * FROM MM_QtyAmtAcct_296b(" +
								p_Account_ID + ", " + 
								p_M_Product_ID + ", '" + 
								DateAcctBegin + "', '" +
								DateAcctEnd + "')";
								
		try {
			pstmtturnover = DB.prepareStatement(sqlturnover.toString(), get_TrxName());
			rsturnover = pstmtturnover.executeQuery();
			
			while (rsturnover.next()) {
				
				//Shifting and Merging
				sheet.shiftRows(row, row + 17, 1);
				sheet.addMergedRegion(new CellRangeAddress(row, row, 0, 4));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 5, 9));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 10, 21));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 22, 23));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 24, 26));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 27, 30));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 31, 35));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 36, 39));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 40, 44));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 45, 48));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 49, 53));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 54, 62));
				
				R = sheet.createRow(row);
				for(int j = 0; j < 63; j++){
					C = R.createCell(j);
					C.setCellStyle(cs);
				}
				
				sheet.getRow(row).getCell(0).setCellValue(rsturnover.getTimestamp("DateAcct"));
				sheet.getRow(row).getCell(0).setCellStyle(ds);
				sheet.getRow(row).getCell(5).setCellValue(rsturnover.getString("DocumentNo"));
				sheet.getRow(row).getCell(10).setCellValue(rsturnover.getString("DocType"));
				sheet.getRow(row).getCell(10).setCellStyle(dts);
				sheet.getRow(row).getCell(22).setCellValue(rsturnover.getString("UOM"));
				sheet.getRow(row).getCell(24).setCellValue(rsturnover.getInt("ColPrice"));
				sheet.getRow(row).getCell(27).setCellValue(rsturnover.getInt("DebitQty"));
				sheet.getRow(row).getCell(31).setCellValue(rsturnover.getInt("DebitAmount"));
				sheet.getRow(row).getCell(36).setCellValue(rsturnover.getInt("CreditQty"));
				sheet.getRow(row).getCell(40).setCellValue(rsturnover.getInt("CreditAmount"));
				sheet.getRow(row).getCell(45).setCellValue(rsturnover.getInt("RemainQty"));
				sheet.getRow(row).getCell(49).setCellValue(rsturnover.getInt("RemainAmount"));
				sheet.getRow(row).getCell(54).setCellValue(rsturnover.getString("Locator"));
				
				row++;
			}
			
		}
		catch (Exception e) {
			log.log(Level.SEVERE, sqlturnover.toString(), e);
		}
		finally {
			DB.close(rsturnover, pstmtturnover);
			rsturnover = null;
			pstmtturnover = null;
		}

		// Write the output to a file
		FileOutputStream fileOut = new FileOutputStream(ResultPath);
		wb.write(fileOut);
		fileOut.close();

		// Open the file with the application associated
		Util.launchFile(new File(ResultPath));
		
		return Msg.translate(m_ctx, "Success");
	}

}
